use sqlx::{PgPool, Error};
use struct_field_names_as_array::FieldNamesAsSlice;

use crate::core::pagination;
use crate::core::psqlx_tools::SqlString;
use crate::model::blog::BlogModel;
use crate::model::category::CategoryModel;
use crate::model::model::Model;
use crate::types::common::CategoryListReq;
use crate::types::frontend::blog_type::{BlogDetail, BlogRow, BlogListResp};


pub struct BlogService;

impl BlogService {
    /// 获取文章分页列表
    /// 参数args CategoryListReq
    /// 正确返回 BlogListResp 数据
    /// 错误返回 sqlx::Error
    pub async fn get_blog_page(db: &PgPool, args: &CategoryListReq) -> Result<BlogListResp, Error> {
        let page = args.page.unwrap_or(1);
        let page_size = args.page_size.unwrap_or(10);
        let cid = args.cid.unwrap_or(0);

        let model = BlogModel::new();
        let category_model = CategoryModel::new();

        // 需要连接查询的字段
        let row_fields = model.show_row_field().split(",");
        let mut join_fields = Vec::new();
        for field in row_fields {
            join_fields.push(format!("{}.{}", model.table(), field));
        }
        join_fields.push(format!("{}.{} AS category_name", category_model.table(), category_model.name()));

  
        let mut sql = SqlString::new(model.table())
        .join_field(&join_fields)
        .join(model.cid(), category_model.table(), category_model.primary_key());
        
        let mut count_sql = SqlString::new(model.table())
        .join(model.cid(), category_model.table(), category_model.primary_key());
        
        // 搜索条件
        if cid > 0 {
            sql = sql.where_eq(model.cid());
            count_sql = count_sql.where_eq(model.cid());
        }
        // 分页数据
        let sql = sql.order_desc(format!("{}.{}", model.table(), model.primary_key()).as_str())
        .pagination(page, page_size);
        // 总数量
        let count_sql = count_sql.count();

        let mut query = sqlx::query_as::<_, BlogRow>(&sql);
        let mut count_query = sqlx::query_scalar::<_, i64>(&count_sql);
        
        // 前端页码query参数
        let mut query_str = String::from("");
        // 搜索条件绑定
        if cid > 0 {
            query = query.bind(cid);
            count_query = count_query.bind(cid);
            query_str.push_str(format!("&{}={}", model.cid(), cid).as_str());
        }

        let res = query.fetch_all(db).await;
        let list = match res {
            Ok(data) => data,
            Err(e) => {
                return Err(e)
            }
        };
        let total = count_query.fetch_one(db).await;
        let total_count = match total {
            Ok(data) => data,
            Err(e) => {
                return Err(e)
            }
        };

        let page_total = pagination::get_total_page(page_size, total_count);
        let page_str = pagination::pagination_link("/category", page, page_total, total_count, &query_str);

        
        let result = BlogListResp{
            paginate: page_str,
            list
        };
        
        Ok(result)
    }

    /// 获取文章详情
    /// 参数 id 文章id
    /// 正确返回 BlogForm 表单数据
    /// 错误返回 sqlx::Error
    pub async fn get_blog_by_id(db: &PgPool, id: i64) -> Result<BlogDetail, Error> {
        let model = BlogModel::new();
        let sql = SqlString::new(model.table())
        .field_slice(BlogDetail::FIELD_NAMES_AS_SLICE)
        .append_primary_key()
        .where_primary_key()
        .select();

        let res = sqlx::query_as::<_, BlogDetail>(&sql)
        .bind(id)
        .fetch_one(db)
        .await;

        res
    }

    /// 获取最新
    pub async fn get_newest(db: &PgPool, num: i32) -> Result<Vec<BlogRow>, Error>  {
        let model = BlogModel::new();
        let category_model = CategoryModel::new();

        // 需要连接查询的字段
        let row_fields = model.show_row_field().split(",");
        let mut join_fields = Vec::new();
        for field in row_fields {
            join_fields.push(format!("{}.{}", model.table(), field));
        }
        join_fields.push(format!("{}.{} AS category_name", category_model.table(), category_model.name()));

  
        let sql = SqlString::new(model.table())
        .join_field(&join_fields)
        .join(model.cid(), category_model.table(), category_model.primary_key())
        .order_primary_key_desc()
        .limit(num);
         
        let res = sqlx::query_as::<_, BlogRow>(&sql).fetch_all(db).await;
        res
    }

    /// 获取相关推荐
    pub async fn get_related(db: &PgPool, cid:i64, num: i32) -> Result<Vec<BlogRow>, Error>  {
        let model = BlogModel::new();
        let category_model = CategoryModel::new();

        // 需要连接查询的字段
        let row_fields = model.show_row_field().split(",");
        let mut join_fields = Vec::new();
        for field in row_fields {
            join_fields.push(format!("{}.{}", model.table(), field));
        }
        join_fields.push(format!("{}.{} AS category_name", category_model.table(), category_model.name()));

        let where_str = format!("{}.{}", model.table(), model.cid());
        let sql = SqlString::new(model.table())
        .join_field(&join_fields)
        .join(model.cid(), category_model.table(), category_model.primary_key())
        .where_eq(&where_str)
        .order_primary_key_desc()
        .limit(num);
         
        let res = sqlx::query_as::<_, BlogRow>(&sql)
        .bind(cid)
        .fetch_all(db).await;
        res
    }

    /// 更新点击
    pub async fn update_hits(db: &PgPool, id:i64, hits: i64) -> Result<bool, Error> {
        let model = BlogModel::new();
        let sql = SqlString::new(model.table())
        .field(model.hits())
        .where_primary_key()
        .update();

        let res = sqlx::query(&sql).bind(id).bind(hits).execute(db).await;
        match res {
            Ok(data) => Ok(data.rows_affected() > 0),
            Err(e) => Err(e)
        }
    }
}
    